package com.example.lightble.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SettingDatabase {
    public final static String TAG = "sno";

    private SQLiteDatabase mDb;
    private final String DB_NAME = "ble";
    private final int DB_VERSION =4;
    
    
    DatabaseOpenHelper doh = null;


    //----------------------------------------------------------------
    private final String GROUPNAME_TABLE_NAME = "grouplist_table";
    private final String GROUP_NAME = "name";
    
    private final String DEVICEPASSWD_TABLE_NAME = "device_passwd_table";
    private final String DEVICE_ADDRESS ="device_address";
    private final String DEVICE_PASSWD="device_passwd";
    
    public static final int WRITE = 0x001;
	public static final int READ = 0x002;

	/**
	 * 
	 * @param context
	 * @param flag
	 *            0 read 1 write
	 */
	public SettingDatabase(Context context, int flag) {
		doh = new DatabaseOpenHelper(context);
		switch (flag) {
		case READ:
			mDb = doh.getReadableDatabase();
			break;
		case WRITE:
			mDb = doh.getWritableDatabase();
			break;
		default:
			mDb = doh.getWritableDatabase();
			break;
		}

	}
	public boolean close() {
		try {
			mDb.close();
			return true;
		} catch (Exception e) {
			Log.w(TAG, "exception - close - " + e.getMessage());
		}
		return false;
	}
    
    private class DatabaseOpenHelper extends SQLiteOpenHelper {

        public DatabaseOpenHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

        public void dropTablesQuery(SQLiteDatabase db) {
            String query1 = "DROP TABLE " + GROUPNAME_TABLE_NAME + ";";
            db.execSQL(query1);
        }
       
        public void createTablesQuery(SQLiteDatabase db) {
            String createUserlistTableQuery = "CREATE TABLE IF NOT EXISTS " +
                    GROUPNAME_TABLE_NAME + " (" +
                    GROUP_NAME + " VARCHAR(200) PRIMARY KEY NOT NULL);";

            db.execSQL(createUserlistTableQuery);
            
            db.execSQL("create table if not exists table_group (group_code varchar(50) ,group_name varchar(50) ,device_address varchar(50),device_name varchar(50))");
            
            db.execSQL("create table if not exists device_passwd_table (device_address varchar(200) not null,device_passwd varchar(200))");
            
            db.execSQL("create table if not exists table_temp_device (device_address varchar(200) not null,device_name varchar(50))");
            
//            String createDevicePasswdlistTableQuery = "CREATE TABLE IF NOT EXISTS " +
//                    DEVICEPASSWD_TABLE_NAME + " (" +
//                    DEVICE_ADDRESS + " VARCHAR(200) PRIMARY KEY NOT NULL, "
//                    + DEVICE_PASSWD + " VARCHAR(200) NOT NULL"
//                    + ");";
            
//            db.execSQL(createDevicePasswdlistTableQuery);
        }
        
        @Override
        public void onCreate(SQLiteDatabase db) {
        	createTablesQuery(db);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            if (oldVersion < DB_VERSION && newVersion == DB_VERSION) {
            	dropTablesQuery(db);
            	createTablesQuery(db);
            }
        }
    }
    /**
     * 
     * @return
     */
    public HashMap<String,String> getDevicePasswdLists()
    {
    	HashMap<String,String> devicePasswdList=new HashMap<String, String>();
         Cursor cursor;
         cursor = mDb.query(
        		 DEVICEPASSWD_TABLE_NAME,
                 new String[] { DEVICE_ADDRESS ,DEVICE_PASSWD},
                 null, null, null, null, null);
         cursor.moveToFirst();
         if (!cursor.isAfterLast()) {
             do {
            	 devicePasswdList.put(cursor.getString(0), cursor.getString(1));
             } while (cursor.moveToNext());
         }
         cursor.close();
         return devicePasswdList;	
    }
    public void addDevicePasswd(String address,String passwd)
    {
    	 ContentValues values = new ContentValues();
         values.put(DEVICE_ADDRESS, address);
         values.put(DEVICE_PASSWD, passwd);
         mDb.insert(DEVICEPASSWD_TABLE_NAME, "NULL", values);	
    }
    public void updateDevicePasswd(String address,String passwd)
    {
    	 ContentValues values = new ContentValues();
         values.put(DEVICE_PASSWD, passwd);
         mDb.update(DEVICEPASSWD_TABLE_NAME, values, DEVICE_ADDRESS + " =?",
                 new String[] { address });
    }
      /**
     * Get all playlists in the database
     * @return
     */
    public ArrayList<String> getGroupNamelists() {
        ArrayList<String> playlists = new ArrayList<String>();
        Cursor cursor;

        cursor = mDb.query(
                GROUPNAME_TABLE_NAME,
                new String[] { GROUP_NAME },
                null, null, null, null, null);
        cursor.moveToFirst();
        if (!cursor.isAfterLast()) {
            do {
                playlists.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return playlists;
    }
   
    public ArrayList<String> getExistsGroupNamelists() {
    	ArrayList<String> playlists = new ArrayList<String>();
    	Cursor cursor;
    	
    	cursor = mDb.query(
    			GROUPNAME_TABLE_NAME,
    			new String[] { GROUP_NAME },
    			null, null, null, null, null);
    	cursor.moveToFirst();
    	if (!cursor.isAfterLast()) {
    		do {
    			playlists.add(cursor.getString(0));
    		} while (cursor.moveToNext());
    	}
    	cursor.close();
    	return playlists;
    }
    /**
     * added by peter on 5 July
     * achieve list of group name already exists
     * @return
     */
    public List<Map<String, String>> query_maplist(String sql) {
    	System.out.println("[sql]--"+sql);
		List<Map<String, String>> list = new LinkedList<Map<String, String>>();
		Cursor cursor = mDb.rawQuery(sql, null);
		
		if (cursor != null) {

			while (cursor.moveToNext()) {

				Map<String, String> map = new HashMap<String, String>();
				for (int col = 0; col < cursor.getColumnCount(); col++) {
					map.put(cursor.getColumnName(col), cursor.getString(col));
				}
				list.add(map);
			}
			cursor.close();
		}
		return list;
	}
    
    
    public String[] getGroupNamelists2() {
        ArrayList<String> playlists = new ArrayList<String>();
        Cursor cursor;

        cursor = mDb.query(
                GROUPNAME_TABLE_NAME,
                new String[] { GROUP_NAME },
                null, null, null, null, null);
        cursor.moveToFirst();
        if (!cursor.isAfterLast()) {
            do {
                playlists.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        String[] toBeStored = playlists.toArray(new String[playlists.size()]); 
        return toBeStored;
    }
   
    /**
     * Add new playlist
     * @param name
     * @return id of the new playlist
     */
    public void addToGroupNamelist(String name) {
        ContentValues values = new ContentValues();
        values.put(GROUP_NAME, name);
        mDb.insert(GROUPNAME_TABLE_NAME, "NULL", values);
    }
    /**
     * Add group
     * @param name
     * @return 
     */
    public boolean addToGroup(String sql) {
    	return execSQL(sql);
    }
    public  boolean execSQL(String sql) {
		try {
			mDb.execSQL(sql);
			return true;
		} catch (Exception e) {
			Log.w(TAG, "exception - execSQL - " + e.getMessage());
		}
		return false;
	}
    public void deleteGroupNamelistItem(String name) {
        mDb.delete(GROUPNAME_TABLE_NAME, GROUP_NAME + "=?",
                new String[] { name });
    }
   
    
    //--------------------------
    
    public void newGroupTable(String groupname)
    {
    	 String createUserlistTableQuery = "CREATE TABLE IF NOT EXISTS " +
    			 groupname + " (" +
                 GROUP_NAME + " VARCHAR(200) PRIMARY KEY NOT NULL);";

    	 mDb.execSQL(createUserlistTableQuery);
    }
    
    public void addDeviceToGroupTable(String user,String tablename)
    {
    	  ContentValues values = new ContentValues();
          values.put(GROUP_NAME, user);
          mDb.insert(tablename, "NULL", values);
    }
    public ArrayList<String> getDeviceFromGroupTable(String tablename) {
        ArrayList<String> playlists = new ArrayList<String>();
        Cursor cursor;

        cursor = mDb.query(
        		tablename,
                new String[] { GROUP_NAME },
                null, null, null, null, null);
        cursor.moveToFirst();
        if (!cursor.isAfterLast()) {
            do {
                playlists.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return playlists;
    }
    public void deleteGroupTable(String tablename)
    {
    	  String query1 = "DROP TABLE " + tablename + ";";
    	  mDb.execSQL(query1);	
    }
}
